﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;
using Bitirme.DAL.Enum;


namespace Bitirme.DAL.Data_Access
{
    public class WorkPackageCollection
    {
        public static int InsertWorkPackage(WorkPackage workpackage)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parName = null;
            SqlParameter parSubject = null;
            SqlParameter parState = null;
            SqlParameter parReturnValue = null;

            int insertedWorkPackageID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertWorkPackage", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = workpackage.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSubject = new SqlParameter();
                parSubject.ParameterName = "@subject";
                parSubject.Value = workpackage.Subject;
                parSubject.SqlDbType = SqlDbType.VarChar;

                parState = new SqlParameter();
                parState.ParameterName = "@state";
                parState.Value = workpackage.PackageState;
                parState.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSubject);
                cmd.Parameters.Add(parState);
                cmd.Parameters.Add(parReturnValue);

                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedWorkPackageID);
            }
            catch (SqlException e)
            {
                insertedWorkPackageID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }


            return insertedWorkPackageID;
        }
        public static Boolean UpdateWorkPackageByID(WorkPackage workpackage)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parName = null;
            SqlParameter parSubject = null;
            SqlParameter parState = null;

            bool updatedWorkPackageID = false;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdateWorkPackageByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = workpackage.ID;
                parID.SqlDbType = SqlDbType.Int;

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = workpackage.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSubject = new SqlParameter();
                parSubject.ParameterName = "@subject";
                parSubject.Value = workpackage.Subject;
                parSubject.SqlDbType = SqlDbType.VarChar;

                parState = new SqlParameter();
                parState.ParameterName = "@state";
                parState.Value = workpackage.PackageState;
                parState.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSubject);
                cmd.Parameters.Add(parState);

                updatedWorkPackageID = true;
                myConnection.Open();
                cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                updatedWorkPackageID = false;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return updatedWorkPackageID;
        }
        public static Boolean DeleteWorkPackageByID(int workpackageID) { 
                   
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parWorkPackageID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteWorkPackageByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@ID";
                parWorkPackageID.Value = workpackageID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parWorkPackageID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }
        public static int GetProjectIDByWorkPackageID(int workpackageID) {
           
            
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parWorkPackageID = null;
            int projectID=-1;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetProjectIDByWorkPackageID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@workpackageID";
                parWorkPackageID.Value = workpackageID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parWorkPackageID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();

                while (myReader.Read())
                {
                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["PROJECT_ID"], 0).ToString(), out ID);
                    projectID = ID;
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return projectID;
        }
        public static Boolean UpdateWorkPackageStateByID(int workPackageID , string state) { 
        
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parState = null;

            bool stateUpdated = false;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdateWorkPackageStateByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                if(state=="Active"){
                
                    parState = new SqlParameter();
                    parState.ParameterName = "@state";
                    parState.Value = State.Pasive;
                    parState.SqlDbType = SqlDbType.Int;
                
                } 
                else {
                
                    parState = new SqlParameter();
                    parState.ParameterName = "@state";
                    parState.Value = State.Active;
                    parState.SqlDbType = SqlDbType.Int;
                    
                }
    
                  
                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = workPackageID;
                parID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parState);

                stateUpdated = true;
                myConnection.Open();
                cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                stateUpdated = false;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return stateUpdated;
        }
    }
}